package com.aplink.generic.database;

import java.util.ArrayList;
import java.util.Locale;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.text.TextUtils;

import com.aplink.generic.cache.ContentDataManager;
import com.aplink.generic.cache.MasterConfigManager;
import com.aplink.generic.dictionary.GenericDictionary;
import com.aplink.generic.interfaces.DatabaseDelete;
import com.aplink.generic.interfaces.DatabaseGet;
import com.aplink.generic.interfaces.DatabaseInsert;
import com.aplink.generic.interfaces.DatabaseUpdate;
import com.aplink.generic.pojo.ContentData;
import com.aplink.generic.pojo.GenericAttribute;
import com.aplink.generic.pojo.GenericObject;
import com.aplink.generic.pojo.MasterConfig;
import com.aplink.generic.pojo.ObjPropInfo;
import com.aplink.generic.pojo.TableRelationship;
import com.aplink.generic.pojo.TableSyncData;
import com.aplink.generic.sharepreference.GenericSharePreference;
import com.aplink.generic.util.Language;
import com.aplink.generic.util.LogUtil;
import com.aplink.generic.webservice.DataResult;
import com.aplink.generic.webservice.HttpResponseStatus;
import com.aplink.generic.webservice.ResponseStatus;

public class DatabaseHelper implements DatabaseInsert, DatabaseGet,
		DatabaseUpdate, DatabaseDelete {
	private static final int GET_ALL = -1;
	private static DatabaseHelper sInstance = null;

	public static DatabaseHelper getInstance() {
		if (DatabaseHelper.sInstance == null) {
			DatabaseHelper.sInstance = new DatabaseHelper();
			if ((DatabaseHelper.sInstance.mDatabase == null)
					|| !DatabaseHelper.sInstance.mDatabase.isOpen()) {
				DatabaseHelper.sInstance.mDatabase = DatabaseManager
						.getInstance().getWritableDatabase();
				DatabaseHelper.sInstance.mCoreDatabaseHelper = new CoreDatabaseHelper(
						DatabaseHelper.sInstance.mDatabase);
			}
		}
		return DatabaseHelper.sInstance;
	}

	private final boolean mAllowSubmitTransaction = true;
	private CoreDatabaseHelper mCoreDatabaseHelper;

	private SQLiteDatabase mDatabase;

	private String baseDelete(final ContentData objPropCol,
			final ContentData objForeignCol, final GenericObject genericObject) {
		if (objForeignCol.getForiegnKeys().isEmpty()) {
			return this.mCoreDatabaseHelper.deleteData(objForeignCol,
					genericObject);
		}
		for (final ObjPropInfo objPropInfo : objForeignCol.getForiegnKeys()) {
			if (objPropInfo.getRelationship() != null) {
				if (objPropInfo.getRelationship().equals(
						TableRelationship.ONE_TO_MANY)) {
					// if one-to-may-> insert main record--> insert foreigns
					final String realRowID = this.mCoreDatabaseHelper
							.deleteData(objForeignCol, genericObject);
					if (realRowID != null) {
						final ObjPropInfo primaryKey = objForeignCol
								.getPrimaryKey(objForeignCol.getMainTable());
						if (primaryKey.isFKey()
								&& (primaryKey.getForeignTable() != null)) {
							final String foriegnTableName = primaryKey
									.getForeignTable();
							final ContentData foriegnTable = ContentData
									.getObjPropColOf(foriegnTableName,
											objPropCol);
							baseDelete(objPropCol, foriegnTable, genericObject);
						}
						return realRowID;
					}
				} else if (objPropInfo.getRelationship().equals(
						TableRelationship.MANY_TO_ONE)) {
					// if many-to-one -> insert foreign, with each foreign
					// insert main record.
					final String mainTableName = objPropInfo.getTable();
					final ContentData mainTableCol = ContentData
							.getObjPropColOf(mainTableName, objPropCol);
					final String foriegnTableName = objPropInfo
							.getForeignTable();
					final ContentData foriegnTable = ContentData
							.getObjPropColOf(foriegnTableName, objPropCol);
					// Insert foreign;
					final String realRowID = this.mCoreDatabaseHelper
							.deleteData(foriegnTable, genericObject);
					if (realRowID != null) {
						this.mCoreDatabaseHelper.deleteData(mainTableCol,
								genericObject);
					}
				}
			}
		}
		return null;
	}

	private long baseInitTable(final GenericObject object,
			final String tableName) {
		try {
			final ContentValues cv = new ContentValues();
			for (int i = 0; i < object.size(); i++) {
				final GenericAttribute attribute = object.valueAt(i);
				final String key = attribute.getName().toString();
				final String value = attribute.getValue().toString();
				if (value != null) {
					cv.put(key, value);
				}
			}
			return this.mDatabase.insert(tableName, null, cv);
		} catch (final SQLException e) {
			throw e;
		}
	}

	private String baseInsert(final ContentData objPropCol,
			final ContentData objForeignCol, final GenericObject genericObject) {
		if (objForeignCol.getForiegnKeys().isEmpty()) {
			return this.mCoreDatabaseHelper.insertData(objForeignCol,
					genericObject);
		}
		for (final ObjPropInfo objPropInfo : objForeignCol.getForiegnKeys()) {
			if (objPropInfo.getRelationship() != null) {
				if (objPropInfo.getRelationship().equals(
						TableRelationship.ONE_TO_MANY)) {
					// if one-to-may-> insert main record--> insert foreigns

					final String realRowID = this.mCoreDatabaseHelper
							.insertData(objForeignCol, genericObject);
					if (realRowID != null) {
						final ObjPropInfo primaryKey = objForeignCol
								.getPrimaryKey(objForeignCol.getMainTable());
						if (primaryKey.isFKey()
								&& (primaryKey.getForeignTable() != null)) {
							final String foriegnTableName = primaryKey
									.getForeignTable();
							final ContentData foriegnTable = ContentData
									.getObjPropColOf(foriegnTableName,
											objPropCol);
							genericObject.put(
									primaryKey.getForeignFullColumnName(),
									realRowID);
							baseInsert(objPropCol, foriegnTable, genericObject);
						}
						return realRowID;
					}
				} else if (objPropInfo.getRelationship().equals(
						TableRelationship.MANY_TO_ONE)) {
					// if many-to-one -> insert foreign, with each foreign
					// insert main record.
					final String mainTableName = objPropInfo.getTable();
					final ContentData mainTableCol = ContentData
							.getObjPropColOf(mainTableName, objPropCol);
					final String foriegnTableName = objPropInfo
							.getForeignTable();
					final ContentData foriegnTable = ContentData
							.getObjPropColOf(foriegnTableName, objPropCol);
					// Insert foreign;
					final String realRowID = this.mCoreDatabaseHelper
							.insertData(foriegnTable, genericObject);
					if (realRowID != null) {
						genericObject.put(objPropInfo.getFullColumnName(),
								realRowID);
						// Insert main record;
						this.mCoreDatabaseHelper.insertData(mainTableCol,
								genericObject);
					}
				}
			}
		}
		return null;
	}

	private String baseUpdate(final ContentData objPropCol,
			final ContentData objForeignCol, final GenericObject genericObject) {
		if (objForeignCol.getForiegnKeys().isEmpty()) {
			return this.mCoreDatabaseHelper.updateData(objForeignCol,
					genericObject);
		}
		for (final ObjPropInfo objPropInfo : objForeignCol.getForiegnKeys()) {
			if (objPropInfo.getRelationship() != null) {
				if (objPropInfo.getRelationship().equals(
						TableRelationship.ONE_TO_MANY)) {
					// if one-to-may-> insert main record--> insert foreigns
					final String realRowID = this.mCoreDatabaseHelper
							.updateData(objForeignCol, genericObject);
					if (realRowID != null) {
						final ObjPropInfo primaryKey = objForeignCol
								.getPrimaryKey(objForeignCol.getMainTable());
						if (primaryKey.isFKey()
								&& (primaryKey.getForeignTable() != null)) {
							final String foriegnTableName = primaryKey
									.getForeignTable();
							final ContentData foriegnTable = ContentData
									.getObjPropColOf(foriegnTableName,
											objPropCol);
							genericObject.get(primaryKey.getFullColumnName())
									.setValue(realRowID);
							baseUpdate(objPropCol, foriegnTable, genericObject);
						}
						return realRowID;
					}
				} else if (objPropInfo.getRelationship().equals(
						TableRelationship.MANY_TO_ONE)) {
					// if many-to-one -> insert foreign, with each foreign
					// insert main record.
					final String mainTableName = objPropInfo.getTable();
					final ContentData mainTableCol = ContentData
							.getObjPropColOf(mainTableName, objPropCol);
					final String foriegnTableName = objPropInfo
							.getForeignTable();
					final ContentData foriegnTable = ContentData
							.getObjPropColOf(foriegnTableName, objPropCol);
					// Insert foreign;
					final String realRowID = this.mCoreDatabaseHelper
							.updateData(foriegnTable, genericObject);
					if (realRowID != null) {
						genericObject.get(objPropInfo.getFullColumnName())
								.setValue(realRowID);
						// Insert main record;
						this.mCoreDatabaseHelper.updateData(mainTableCol,
								genericObject);
					}
				}
			}
		}
		return null;
	}

	private void beginTransaction() {
		if (this.mDatabase.isOpen()) {
			if (!this.mDatabase.inTransaction()) {
				this.mDatabase.beginTransaction();
			}
		}
	}

	private String buildWhereCondition(final ContentData objPropCol,
			final GenericObject genericObject) {
		final String table = objPropCol.getMainTable();
		final ObjPropInfo createdTime = objPropCol.getCreatedTime(table);
		final SqlMethods sqlMethods = SqlMethods.getInstance();
		if (createdTime == null) {
			final ObjPropInfo primaryKey = objPropCol.getPrimaryKey(table);
			if (!TextUtils.isEmpty(genericObject.getValue(primaryKey
					.getFullColumnName()))) {
				final String value = genericObject.getValue(
						primaryKey.getFullColumnName()).toString();
				sqlMethods.equal(primaryKey, value);
			}
		} else {
			final String value = genericObject.getValue(
					createdTime.getFullColumnName()).toString();
			sqlMethods.equal(createdTime, value);
		}
		return sqlMethods.toString();
	}

	@Override
	public void clearSyncStatus() {
		try {
			final MasterConfig masterConfig = MasterConfigManager.getInstance()
					.get(GenericSharePreference.getInstance()
							.getMasterConfigFileID());
			beginTransaction();
			for (int i = 0; i < masterConfig.getWebserviceConfig()
					.getTableSyncData().size(); i++) {
				final TableSyncData tableSyncData = masterConfig
						.getWebserviceConfig().getTableSyncData().valueAt(i);

				final int contentDataFileId = tableSyncData
						.getWebserviceFunctionID();
				final ContentData objPropCol = ContentDataManager.getInstance()
						.get(contentDataFileId);

				for (final ObjPropInfo objPropInfo : objPropCol.getSyncStatus()) {
					final String mainTableName = objPropInfo.getTable();
					final ObjPropInfo syncStatusColumn = objPropCol
							.getSyncStatus(mainTableName);
					final ContentValues contentValues = new ContentValues();
					contentValues.putNull(syncStatusColumn.getColumnName());
					final SqlMethods whereClause = SqlMethods.getInstance()
							.equal(syncStatusColumn, "delete");
					this.mDatabase.delete(mainTableName,
							whereClause.toString(), null);
					final int rowEffected = this.mDatabase.update(
							mainTableName, contentValues, null, null);
					if (rowEffected > 0) {
						LogUtil.info(LogUtil.TAG_DATABASE,
								"clear sync status for table:" + mainTableName,
								"clearSyncStatus", getClass());
					}
				}
			}
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"Insert object", getClass());
		} finally {
			submitTransaction();
		}
	}

	@Override
	public boolean clearSyncStatus(final ArrayList<DataResult> dataResults) {
		if (dataResults.isEmpty()) {
			return true;
		}
		try {
			for (final DataResult result : dataResults) {
				if (result.getServerResponse().equals(
						HttpResponseStatus.SUCCESS)) {
					if (result.getResponseStatus().getStatus() == ResponseStatus.SUCCESS) {
						final GenericObject object = result.getGenericObject();
						final ContentData objPropCol = result.getContentData();
						final String mainTableName = objPropCol.getMainTable();
						final ObjPropInfo syncColumn = objPropCol
								.getSyncStatus(mainTableName);
						final String action = object.getValue(syncColumn);
						final String where = buildWhereCondition(objPropCol,
								object);
						if (action.equals("delete")) {
							this.delete(objPropCol, where);
						} else {
							final ContentValues values = new ContentValues();
							values.putNull(syncColumn.getColumnName());
							this.mDatabase.update(mainTableName, values, where,
									null);
						}
					}
				}
			}
			return true;
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"Clear SyncStatus object", getClass());
		} finally {
			submitTransaction();
		}
		return false;
	}

	@Override
	public void clearSyncStatus(final GenericObject object,
			final ContentData objPropCol) {
		try {
			beginTransaction();
			for (final ObjPropInfo objPropInfo : objPropCol.getSyncStatus()) {
				final String mainTableName = objPropInfo.getTable();
				final ObjPropInfo syncStatusColumn = objPropCol
						.getSyncStatus(mainTableName);
				final ContentValues contentValues = new ContentValues();
				contentValues.putNull(syncStatusColumn.getColumnName());
				final SqlMethods whereClause = SqlMethods.getInstance().equal(
						syncStatusColumn, "delete");
				this.mDatabase.delete(mainTableName, whereClause.toString(),
						null);
				final int rowEffected = this.mDatabase.update(mainTableName,
						contentValues, null, null);
				if (rowEffected > 0) {
					LogUtil.info(LogUtil.TAG_DATABASE,
							"clear sync status for table:" + mainTableName,
							"clearSyncStatus");
				}
			}
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"Insert object", getClass());
		} finally {
			submitTransaction();
		}
	}

	@Override
	public String delete(final ContentData objPropCol) {
		try {
			beginTransaction();
			String id = null;
			for (final ObjPropInfo objPropInfo : objPropCol.getForiegnKeys()) {
				id = String.valueOf(this.mDatabase.delete(
						objPropInfo.getTable(), null, null));
			}
			this.mDatabase.delete(objPropCol.getMainTable(), null, null);
			return id;
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"delete object", getClass());
		} finally {
			submitTransaction();
		}
		return null;
	}

	@Override
	public ArrayList<String> delete(final ContentData objPropCol,
			final ArrayList<GenericObject> genericObjects) {
		final ArrayList<String> ids = new ArrayList<String>();
		try {
			beginTransaction();
			final String mainTable = objPropCol.getMainTable();
			final ContentData mainTableProperty = ContentData.getObjPropColOf(
					mainTable, objPropCol);
			for (final GenericObject object : genericObjects) {
				final String id = baseDelete(objPropCol, mainTableProperty,
						object);
				if (id == null) {
					LogUtil.info(LogUtil.TAG_DATABASE, " can't delete "
							+ object.getTableName() + "  to database",
							"deleteList");
				} else {
					LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
							+ " was delete, id=" + id, "deleteList");
					ids.add(id);
				}
			}
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"delete object", getClass());
		} finally {
			submitTransaction();
		}
		return ids;
	}

	@Override
	public void delete(final ContentData objPropCol, final String sqlMethods) {
		try {
			beginTransaction();
			final String mainTable = objPropCol.getMainTable();
			this.mDatabase.delete(mainTable, sqlMethods, null);
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"delete object", getClass());
		} finally {
			submitTransaction();
		}
	}

	@Override
	public String delete(final GenericObject object,
			final ContentData objPropCol) {
		try {
			beginTransaction();
			final String mainTable = objPropCol.getMainTable();
			final ContentData mainTableProperty = ContentData.getObjPropColOf(
					mainTable, objPropCol);
			final String id = baseDelete(objPropCol, mainTableProperty, object);
			if (id == null) {
				LogUtil.info(LogUtil.TAG_DATABASE,
						" can't delete " + object.getTableName()
								+ "  to database", "delete");
			} else {
				LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
						+ " was delete, id=" + id, "delete");
			}
			return id;
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"delete object", getClass());
		} finally {
			submitTransaction();
		}
		return null;
	}

	@Override
	public String delete(final String table, String whereCondition,
			String[] whereArgs) {
		if (table != null) {
			try {
				beginTransaction();
				return String.valueOf(this.mDatabase.delete(table,
						whereCondition, whereArgs));
			} catch (final SQLException e) {
				e.printStackTrace();
			} finally {
				submitTransaction();
			}
		}
		return null;
	}

	/**
	 * return an Generic object with where clause conditions. If object is not
	 * exists, return null.
	 */
	@Override
	public GenericObject get(final ContentData propCol, final String whereClause) {
		try {
			final ArrayList<GenericObject> genericObjects = this.mCoreDatabaseHelper
					.getData(propCol, whereClause, 1, true);
			if (genericObjects.isEmpty()) {
				return new GenericObject();
			}
			return genericObjects.get(0);
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "get",
					getClass());
		}
		return new GenericObject();
	}

	/**
	 * return an Generic object with where clause conditions. If object is not
	 * exists, return null.
	 */
	@Override
	public GenericObject get(final ContentData propCol,
			final String whereClause, final boolean isCheckDataRules) {
		try {
			final ArrayList<GenericObject> genericObjects = this.mCoreDatabaseHelper
					.getData(propCol, whereClause, 1, isCheckDataRules);
			if (genericObjects.isEmpty()) {
				return null;
			}
			return genericObjects.get(0);
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "get",
					getClass());
		}
		return null;
	}

	@Override
	public ArrayList<GenericObject> getList(final ContentData propCol,
			String whereClause) {
		try {
			if ((whereClause == null) || whereClause.isEmpty()) {
				whereClause = null;
			}
			return this.mCoreDatabaseHelper.getData(propCol, whereClause,
					DatabaseHelper.GET_ALL);
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "getList",
					getClass());
		}
		return new ArrayList<GenericObject>();
	}

	public GenericDictionary<String, GenericDictionary<String, String>> getTranslationDictionary() {
		GenericDictionary<String, GenericDictionary<String, String>> translations = new GenericDictionary<>();
		try {
			String queryLanguages = SQLiteQueryBuilder.buildQueryString(true,
					Language.TABLE_LANGUAGE,
					new String[] { Language.COLUMN_LANGUAGE_NAME }, null, null,
					null, null, null);
			String queryLangageTranslatetion = SQLiteQueryBuilder
					.buildQueryString(true, Language.TABLE_TRANSLATION, null,
							null, null, null, null, null);
			Cursor translationCursor = mDatabase.rawQuery(
					queryLangageTranslatetion, null);
			Cursor languageCusor = mDatabase.rawQuery(queryLanguages, null);
			while (translationCursor.moveToNext()) {
				GenericDictionary<String, String> dictionary = new GenericDictionary<>();
				String key = null;
				String translationValue = null;
				String languageName = null;
				while (languageCusor.moveToNext()) {
					languageName = languageCusor.getString(languageCusor
							.getColumnIndex(Language.COLUMN_LANGUAGE_NAME));
					translationValue = translationCursor
							.getString(translationCursor
									.getColumnIndex(languageName));
					if (languageName
							.equalsIgnoreCase(Language.DEFAULT_LANGUAGE)) {
						key = translationValue;
					} else {
						dictionary.put(languageName, translationValue);
					}
				}
				languageCusor.moveToPosition(-1);
				translations.put(key.trim().toLowerCase(Locale.getDefault())
						.replaceAll(":", ""), dictionary);
			}
			languageCusor.close();
			translationCursor.close();
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"update object", getClass());
		}
		return translations;
	}

	@Override
	public ArrayList<String> initTable(
			final ArrayList<GenericObject> genericObjects) {
		final String tableName = genericObjects.get(0).getTableName();
		return this.initTable(genericObjects, tableName);
	}

	@Override
	public ArrayList<String> initTable(
			final ArrayList<GenericObject> genericObjects, final String table) {
		final ArrayList<String> ids = new ArrayList<String>();
		if ((table == null) || (genericObjects.size() == 0)) {
			return null;
		}
		try {
			beginTransaction();
			this.mDatabase.delete(table, null, null);
			for (final GenericObject object : genericObjects) {
				final long id = baseInitTable(object, table);
				if (id != -1) {
					ids.add(String.valueOf(id));
					LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
							+ " was inserted, id=" + id, "insertObject");
				} else {
					LogUtil.info(LogUtil.TAG_DATABASE, "Can't not insert "
							+ object.getTableName(), "insertObject");
				}
			}
		} catch (final SQLException e) {
			e.printStackTrace();
		} finally {
			submitTransaction();
		}
		return ids;
	}

	@Override
	public String initTable(final GenericObject object, final String table) {
		if (object.size() == 0) {
			return null;
		}
		try {
			beginTransaction();
			final String id = String.valueOf(baseInitTable(object, table));
			if (id != null) {
				LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
						+ " was init, id=" + id, "initTable");
			} else {
				LogUtil.info(LogUtil.TAG_DATABASE,
						" can't init " + object.getTableName()
								+ "  to database", "initTable");
			}
			return id;
		} catch (final SQLException e) {
			e.printStackTrace();
		} finally {
			submitTransaction();
		}
		return null;
	}

	@Override
	public ArrayList<String> initTables(final ArrayList<DataResult> dataResults) {
		final boolean isOfflineMode = GenericSharePreference.getInstance()
				.isOfflineMode();
		final ArrayList<String> ids = new ArrayList<String>();
		try {
			beginTransaction();
			GenericSharePreference.getInstance().setOfflineMode(false);
			for (final DataResult dataResult : dataResults) {
				if (dataResult.getServerResponse().equals(
						HttpResponseStatus.SUCCESS)) {
					if (dataResult.getContentData() == null) {
						final ArrayList<GenericObject> genericObject = dataResult
								.getData();
						if (genericObject.isEmpty()) {
							continue;
						}
						final String tableName = genericObject.get(0)
								.getTableName();
						this.mDatabase.delete(tableName, null, null);
						for (final GenericObject object : genericObject) {
							final long id = baseInitTable(object, tableName);
							if (id != -1) {
								LogUtil.info(LogUtil.TAG_DATABASE,
										object.getTableName()
												+ " was inserted, id=" + id,
										"insertObject");
								ids.add(id + "");
							} else {
								LogUtil.info(
										LogUtil.TAG_DATABASE,
										"Can't not insert "
												+ object.getTableName(),
										"insertObject");
							}
						}
					} else {
						final ArrayList<GenericObject> genericObjects = dataResult
								.getData();
						if (genericObjects.isEmpty()) {
							continue;
						}
						final ContentData contentData = dataResult
								.getContentData();
						final String mainTable = contentData.getMainTable();
						final ContentData mainTableProperty = ContentData
								.getObjPropColOf(mainTable, contentData);
						for (final GenericObject object : genericObjects) {
							final String id = baseInsert(contentData,
									mainTableProperty, object);
							if (id == null) {
								LogUtil.info(
										LogUtil.TAG_DATABASE,
										" can't insert "
												+ object.getTableName()
												+ "  to database",
										"insertObject");
							} else {
								LogUtil.info(LogUtil.TAG_DATABASE,
										object.getTableName()
												+ " was inserted, id=" + id,
										"insertObject");
								ids.add(id);
							}
						}
					}
				}
			}
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "initTables",
					getClass());
		} finally {
			GenericSharePreference.getInstance().setOfflineMode(isOfflineMode);
			submitTransaction();
		}
		return ids;
	}

	@Override
	public ArrayList<String> insert(
			final ArrayList<GenericObject> genericObjects,
			final ContentData objPropCol) {
		final ArrayList<String> ids = new ArrayList<String>();
		try {
			beginTransaction();
			final String mainTable = objPropCol.getMainTable();
			final ContentData mainTableProperty = ContentData.getObjPropColOf(
					mainTable, objPropCol);
			for (final GenericObject object : genericObjects) {
				final String id = baseInsert(objPropCol, mainTableProperty,
						object);
				if (id == null) {
					LogUtil.info(LogUtil.TAG_DATABASE, " can't insert "
							+ object.getTableName() + "  to database",
							"insertObject");
				} else {
					LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
							+ " was inserted, id=" + id, "insertObject");
					ids.add(id);
				}
			}
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"Insert object", getClass());
		} finally {
			submitTransaction();
		}
		return ids;
	}

	@Override
	public String insert(final GenericObject object,
			final ContentData objPropCol) {
		try {
			final String mainTable = objPropCol.getMainTable();
			beginTransaction();
			final ContentData mainTableProperty = ContentData.getObjPropColOf(
					mainTable, objPropCol);
			final String id = baseInsert(objPropCol, mainTableProperty, object);

			if (id == null) {
				LogUtil.info(LogUtil.TAG_DATABASE,
						" can't insert " + object.getTableName()
								+ "  to database", "insertObject");
			} else {
				LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
						+ " was inserted, id=" + id, "insertObject");
			}
			return id;
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"Insert object", getClass());
		} finally {
			submitTransaction();
		}
		return null;
	}

	@Override
	public String insert(final GenericObject object,
			final ContentData objPropCol,
			final ArrayList<GenericObject> listImages,
			final ContentData galleryContentData) {
		try {
			final String mainTable = objPropCol.getMainTable();
			beginTransaction();
			final ContentData mainTableProperty = ContentData.getObjPropColOf(
					mainTable, objPropCol);
			final String id = baseInsert(objPropCol, mainTableProperty, object);

			if (id == null) {
				LogUtil.info(LogUtil.TAG_DATABASE,
						" can't insert " + object.getTableName()
								+ "  to database", "insertObject");
			} else {
				LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
						+ " was inserted, id=" + id, "insertObject");
			}
			final String column = galleryContentData.getMainTable() + "."
					+ objPropCol.getPrimaryKey(mainTable).getColumnName();
			for (final GenericObject genericObject : listImages) {
				genericObject.put(column, id);
				baseInsert(galleryContentData, galleryContentData,
						genericObject);
			}
			return id;
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"Insert object", getClass());
		} finally {
			submitTransaction();
		}
		return null;
	}

	@Override
	public String insertDownloaded(final DataResult dataResult) {
		final boolean isOfflineMode = GenericSharePreference.getInstance()
				.isOfflineMode();
		try {
			GenericSharePreference.getInstance().setOfflineMode(false);
			final ArrayList<GenericObject> genericObjects = dataResult
					.getData();
			final ContentData contentData = dataResult.getContentData();
			final String mainTable = contentData.getMainTable();
			final ContentData mainTableProperty = ContentData.getObjPropColOf(
					mainTable, contentData);
			final MasterConfig masterConfig = MasterConfigManager.getInstance()
					.get(GenericSharePreference.getInstance()
							.getMasterConfigFileID());
			final TableSyncData tableSyncData = masterConfig
					.getWebserviceConfig().getTableSyncData().get(mainTable);
			boolean isCheckExists = true;
			if (tableSyncData.isClearAll()) {
				final ArrayList<GenericObject> objects = getList(contentData,
						null);
				this.delete(contentData, objects);
				isCheckExists = false;
			}
			beginTransaction();
			final ArrayList<String> ids = new ArrayList<String>();
			for (final GenericObject object : genericObjects) {
				if (isCheckExists) {
					if (this.isExists(object, mainTableProperty)) {
						continue;
					}
				}
				final String id = baseInsert(contentData, mainTableProperty,
						object);
				if (id == null) {
					LogUtil.info(LogUtil.TAG_DATABASE, " can't insert "
							+ object.getTableName() + "  to database",
							"insertObject");
				} else {
					LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
							+ " was inserted, id=" + id, "insertObject");
					ids.add(id);
				}
			}
			if (ids.isEmpty()) {
				return null;
			}
			return ids.get(0);
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"Insert object", getClass());
		} finally {
			GenericSharePreference.getInstance().setOfflineMode(isOfflineMode);
			submitTransaction();
		}
		return null;
	}

	@Override
	public ArrayList<String> insertDownloadedList(
			final ArrayList<DataResult> dataResults) {
		final boolean isOfflineMode = GenericSharePreference.getInstance()
				.isOfflineMode();
		final ArrayList<String> ids = new ArrayList<String>();
		try {
			GenericSharePreference.getInstance().setOfflineMode(false);
			beginTransaction();
			for (final DataResult dataResult : dataResults) {
				if (dataResult.getServerResponse().equals(
						HttpResponseStatus.SUCCESS)) {
					if (dataResult.getContentData() == null) {
						final ArrayList<GenericObject> genericObject = dataResult
								.getData();
						final String tableName = dataResult.getTable();
						this.mDatabase.delete(tableName, null, null);
						for (final GenericObject object : genericObject) {
							final long id = baseInitTable(object, tableName);
							if (id != -1) {
								LogUtil.info(LogUtil.TAG_DATABASE,
										object.getTableName()
												+ " was inserted, id=" + id,
										"insertObject");
								ids.add(id + "");
							} else {
								LogUtil.info(
										LogUtil.TAG_DATABASE,
										"Can't not insert "
												+ object.getTableName(),
										"insertObject");
							}
						}
					} else {
						final ArrayList<GenericObject> genericObjects = dataResult
								.getData();
						final ContentData contentData = dataResult
								.getContentData();
						final String mainTable = contentData.getMainTable();
						final ContentData mainTableProperty = ContentData
								.getObjPropColOf(mainTable, contentData);
						final MasterConfig masterConfig = MasterConfigManager
								.getInstance().get(
										GenericSharePreference.getInstance()
												.getMasterConfigFileID());
						final TableSyncData tableSyncData = masterConfig
								.getWebserviceConfig().getTableSyncData()
								.get(mainTable);
						boolean isCheckExists = true;
						if (tableSyncData.isClearAll()) {
							final ArrayList<GenericObject> objects = getList(
									contentData, null);
							this.delete(contentData, objects);
							isCheckExists = false;
						}
						beginTransaction();
						for (final GenericObject object : genericObjects) {
							if (isCheckExists) {
								if (this.isExists(object, mainTableProperty)) {
									continue;
								}
							}
							final String id = baseInsert(contentData,
									mainTableProperty, object);
							if (id == null) {
								LogUtil.info(
										LogUtil.TAG_DATABASE,
										" can't insert "
												+ object.getTableName()
												+ "  to database",
										"insertObject");
							} else {
								LogUtil.info(LogUtil.TAG_DATABASE,
										object.getTableName()
												+ " was inserted, id=" + id,
										"insertObject");
								ids.add(id);
							}
						}
					}
				}
			}
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"Insert object", getClass());
		} finally {
			GenericSharePreference.getInstance().setOfflineMode(isOfflineMode);
			submitTransaction();
		}
		return ids;
	}

	@Override
	public boolean isExists(final ContentData objPropCol,
			final String whereClause) {
		try {
			return this.mCoreDatabaseHelper.isExists(objPropCol, whereClause);
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "getList",
					getClass());
		} finally {
			submitTransaction();
		}
		return false;
	}

	@Override
	public boolean isExists(final GenericObject genericObject,
			final ContentData objPropCol) {
		try {
			return this.mCoreDatabaseHelper.isExists(genericObject, objPropCol);
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "getList",
					getClass());
		}
		return false;
	}

	public void releaseDatabase() {
		if ((this.mDatabase != null) && this.mDatabase.isOpen()) {
			if (this.mDatabase.inTransaction()) {
				if (this.mAllowSubmitTransaction) {
					this.mDatabase.setTransactionSuccessful();
					this.mDatabase.endTransaction();
				}
			}
			DatabaseManager.getInstance().close();
			DatabaseHelper.sInstance = null;
		}
	}

	@Override
	public ArrayList<GenericObject> selectDistinct(final String table,
			final ContentData objPropCol, final String whereClause) {
		try {
			if ((objPropCol == null) || objPropCol.isEmpty()) {
				return null;
			}
			final ArrayList<String> queriedColumns = new ArrayList<String>();
			for (int i = 0; i < objPropCol.size(); i++) {
				final ObjPropInfo objPropInfo = objPropCol.valueAt(i);
				if (objPropInfo.getTable().equalsIgnoreCase(table)) {
					queriedColumns.add(objPropInfo.getFullColumnName());
				}
			}
			final ArrayList<GenericObject> genericObjectList = new ArrayList<GenericObject>();
			Cursor cursor;
			String queryTables = null;
			if (objPropCol.getJoins().isEmpty()) {
				queryTables = table;
			} else {
				queryTables = objPropCol.getJoins();
			}
			final String queryString = SQLiteQueryBuilder.buildQueryString(
					true, queryTables,
					queriedColumns.toArray(new String[queriedColumns.size()]),
					whereClause, objPropCol.getGroupBy(), null,
					objPropCol.getOderBy(), null);
			cursor = this.mDatabase.rawQuery(queryString, null);
			LogUtil.info(LogUtil.TAG_DATABASE, queryString, "buildQueryString");

			if (cursor != null) {
				cursor.moveToFirst();
				final int numRows = cursor.getCount();
				for (int i = 0; i < numRows; ++i) {
					final GenericObject genericObject = new GenericObject();
					genericObject.setTableName(table);
					for (int j = 0; j < objPropCol.size(); j++) {
						final ObjPropInfo objPropInfo = objPropCol.valueAt(j);
						if (objPropInfo.getTable().equalsIgnoreCase(table)) {
							final String name = objPropInfo.getColumnName();
							String value = cursor.getString(cursor
									.getColumnIndex(name));
							if (value == null) {
								value = "";
							}
							final GenericAttribute attribute = GenericAttribute
									.obtain(value, objPropInfo);
							genericObject.put(objPropInfo.getFullColumnName(),
									attribute);
						}
					}
					genericObjectList.add(genericObject);
					cursor.moveToNext();
				}
				cursor.close();
				cursor = null;
				return genericObjectList;
			}
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(), "Load data",
					getClass());
		}
		return null;
	}

	private void submitTransaction() {
		if ((this.mDatabase != null) && this.mDatabase.isOpen()) {
			if (this.mDatabase.inTransaction()) {
				if (this.mAllowSubmitTransaction) {
					this.mDatabase.setTransactionSuccessful();
					this.mDatabase.endTransaction();
				}
			}
		}
	}

	@Override
	public String update(final GenericObject object,
			final ContentData objPropCol) {
		try {
			final String mainTable = objPropCol.getMainTable();
			beginTransaction();
			final ContentData mainTableProperty = ContentData.getObjPropColOf(
					mainTable, objPropCol);
			final String id = baseUpdate(objPropCol, mainTableProperty, object);
			if (id == null) {
				LogUtil.info(LogUtil.TAG_DATABASE,
						" can't update " + object.getTableName()
								+ "  to database", "update");
			} else {
				LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
						+ " was updated, id=" + id, "update");
			}
			return id;
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"update object", getClass());
		} finally {
			submitTransaction();
		}
		return null;
	}

	@Override
	public String update(final GenericObject object,
			final ContentData objPropCol,
			final ArrayList<GenericObject> deletes,
			final ArrayList<GenericObject> inserts,
			final ContentData galleryContentData) {
		try {
			final String mainTable = objPropCol.getMainTable();
			beginTransaction();
			final ContentData mainTableProperty = ContentData.getObjPropColOf(
					mainTable, objPropCol);
			final String id = baseUpdate(objPropCol, mainTableProperty, object);
			if (id == null) {
				LogUtil.info(LogUtil.TAG_DATABASE,
						" can't insert " + object.getTableName()
								+ "  to database", "insertObject");
			} else {
				LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
						+ " was update, id=" + id, "insertObject");
			}

			for (final GenericObject deleteImage : deletes) {
				baseDelete(galleryContentData, galleryContentData, deleteImage);
			}

			final String column = galleryContentData.getMainTable() + "."
					+ objPropCol.getPrimaryKey(mainTable).getColumnName();

			for (final GenericObject insertImage : inserts) {
				insertImage.put(column, id);
				baseInsert(galleryContentData, galleryContentData, insertImage);
			}
			return id;
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"Insert object", getClass());
		} finally {
			submitTransaction();
		}
		return null;
	}

	@Override
	public ArrayList<String> updateList(
			final ArrayList<GenericObject> genericObjects,
			final ContentData objPropCol) {
		final ArrayList<String> ids = new ArrayList<String>();
		try {
			beginTransaction();
			final String mainTable = objPropCol.getMainTable();
			final ContentData mainTableProperty = ContentData.getObjPropColOf(
					mainTable, objPropCol);
			for (final GenericObject object : genericObjects) {
				final String id = baseUpdate(objPropCol, mainTableProperty,
						object);
				if (id == null) {
					LogUtil.info(LogUtil.TAG_DATABASE, " can't update "
							+ object.getTableName() + "  to database",
							"updateList");
				} else {
					LogUtil.info(LogUtil.TAG_DATABASE, object.getTableName()
							+ " was update, id=" + id, "updateList");
					ids.add(id);
				}
			}
		} catch (final SQLException e) {
			LogUtil.error(LogUtil.TAG_DATABASE, e.getMessage(),
					"update object", getClass());
		} finally {
			submitTransaction();
		}
		return ids;
	}
}
